package com.qihoo.finance.lcs.sqltools;

import com.google.common.base.Joiner;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.LineIterator;

import java.io.IOException;
import java.net.URL;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SqlDepend {


    public static void main(String[] args) throws IOException {
        ClassLoader classLoader = SqlDepend.class.getClassLoader();
        URL url = classLoader.getResource("./standard_sql.csv");

        Pattern pattern = Pattern.compile("(\\S*)\\t(\\S*)\\t(.*)", Pattern.DOTALL);

        Map<String, Set<String>> dbTableMap = new HashMap<>();
        List<ReportDatabaseTableInfo> reportDatabaseTableInfos = new ArrayList<>(1000);

        LineIterator lineIterator = FileUtils.lineIterator(FileUtils.toFile(url), "utf-8");
        while (lineIterator.hasNext()) {
            String line = lineIterator.nextLine();
            Matcher matcher = pattern.matcher(line);
            if (!matcher.matches()) {
                continue;
            }
            String reportId = matcher.group(1);
            String reportName = matcher.group(2);
            String sql = matcher.group(3).replaceAll("->>", ">>").replaceAll("[（）]", "");
            List<String> tableList = null;
            try {
                tableList = findTables(sql);
            } catch (JSQLParserException e) {
                //ignore
            }
            reportDatabaseTableInfos.add(new ReportDatabaseTableInfo(reportId, reportName, tableList));

            if (tableList == null || tableList.size() <= 0) {
                continue;
            }
            for (String dbAndTable : tableList) {
                String[] splits = dbAndTable.split("\\.");
                if(splits.length == 2){
                    String db = splits[0].trim();
                    if (!dbTableMap.containsKey(db)) {
                        dbTableMap.put(db, new HashSet<>());
                    }
                    Set<String> tables = dbTableMap.get(db);
                    tables.add(splits[1].trim());
                }
            }
        }


        for(ReportDatabaseTableInfo reportDatabaseTableInfo: reportDatabaseTableInfos){
            String tableStr = Objects.nonNull(reportDatabaseTableInfo.databaseTables) ? Joiner.on(", ").join(reportDatabaseTableInfo.databaseTables) : "";
            System.out.println(String.format("reportId:%s, reportName:%s, tables:%s", reportDatabaseTableInfo.reportId, reportDatabaseTableInfo.reportName, tableStr));
        }

        for(Map.Entry<String, Set<String>> entry: dbTableMap.entrySet()){
            String dbTableStr = Joiner.on(", ").join(entry.getValue());
            System.out.println(String.format("db:%s, tables:%s", entry.getKey(), dbTableStr));
        }

    }

    public static List<String> findTables(String selectSql) throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(selectSql);
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableList = tablesNamesFinder.getTableList(statement);
        return tableList;
    }

    private static class ReportDatabaseTableInfo{
        private String reportId;
        private String reportName;
        private List<String> databaseTables;

        public ReportDatabaseTableInfo(String reportId, String reportName, List<String> databaseTables) {
            this.reportId = reportId;
            this.reportName = reportName;
            this.databaseTables = databaseTables;
        }

        public String getReportId() {
            return reportId;
        }

        public ReportDatabaseTableInfo setReportId(String reportId) {
            this.reportId = reportId;
            return this;
        }

        public String getReportName() {
            return reportName;
        }

        public ReportDatabaseTableInfo setReportName(String reportName) {
            this.reportName = reportName;
            return this;
        }

        public List<String> getDatabaseTables() {
            return databaseTables;
        }

        public ReportDatabaseTableInfo setDatabaseTables(List<String> databaseTables) {
            this.databaseTables = databaseTables;
            return this;
        }
    }

}
